package database.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;

import database.DBConnection;

import model.subjects.Enterprise;
import model.subjects.Person;
import model.subjects.Subject;

public class SubjectDAO {
	static DBConnection db = new DBConnection();
	
	
	public static Subject[] getSubjectsFromDB(String name){
		String sql = "SELECT P.subject_id ,P.subject_name, P.subject_type FROM "
				+"(SELECT person AS subject_id, 'isik'  AS subject_type, last_name AS subject_name FROM person WHERE UPPER(last_name) LIKE UPPER('%"+name+"%') "
				+"UNION SELECT enterprise AS subject_id, 'ettevote'  AS subject_type, name AS subject_name FROM enterprise WHERE UPPER(name) LIKE UPPER('%"+name+"%' )) AS P;";
		Subject[] subjects = null;
		try{
			ResultSet resultSet = db.executeQuery(sql);
			subjects = toSubjects(resultSet);
		}catch(SQLException e){
			System.out.println("A problem with SQL statement in finding subjects");
			e.printStackTrace();
		}
		return subjects;
	}
	public static Subject[] toSubjects(ResultSet resultSet){
		ArrayList<Subject> subjectsList = new ArrayList<Subject>();
		int i=0;
		try {
			while (resultSet.next()) {
				Subject subject;
				System.out.println("Subject_type on: " +resultSet.getString("subject_type"));
				if(resultSet.getString("subject_type").equals("isik")){
					subject = new Person();
				}else{
					subject = new Enterprise();
				}
				subject.setName(resultSet.getString("subject_name"));
				subject.setId(resultSet.getInt("subject_id"));
				subjectsList.add(subject);
				i++;
			}

		}catch (Exception ex) {
			ex.printStackTrace();
			// MyLogger.Log("Auth.UserAuth():",ex.getMessage());
		}
		Subject[] subjects = new Subject[i];
		return subjectsList.toArray(subjects);
		
	}
	public ResultSet generalSearch(Map<String,String[]> parameters){
		String sql="";
		String firstName = parameters.get("first_name")[0];
		String name = parameters.get("name")[0];
		String contact = parameters.get("contact")[0];
		String country = parameters.get("country")[0]; 
		String county = parameters.get("county")[0];
		String townVillage = parameters.get("town_village")[0];
		String streetAddress = parameters.get("street_address")[0];
		String zipcode = parameters.get("zipcode")[0];
		sql += "SELECT DISTINCT subject_id, first_name, last_name, subject_type FROM (";
		if(firstName != ""){
			//Get all persons whose first name includes the given parameter
			sql += "(SELECT P.person AS subject_id, P.first_name, P.last_name, 1 AS subject_type FROM person P WHERE UPPER(first_name) LIKE UPPER('%"+firstName+"%'))";
		}
		if(name != ""){
			if(firstName != ""){
				sql += "INTERSECT ";
			}
			//Search from persons and enterprises where last name or enterprise name is equal to param "name"
			sql += "SELECT P.subject_id ,P.subject_name, P.subject_type FROM "
					+"(SELECT person AS subject_id, 'isik'  AS subject_type, last_name AS subject_name FROM person WHERE UPPER(last_name) LIKE UPPER('%"+name+"%') "
					+"UNION SELECT enterprise AS subject_id, 'ettevote'  AS subject_type, name AS subject_name FROM enterprise WHERE UPPER(name) LIKE UPPER('%"+name+"%' )) AS P ";
		}
		if(contact != ""){
			if(firstName != "" || name != ""){
				sql += "UNION ";
			}
			sql += "SELECT P.person,P.first_name, P.last_name,CT.type_name,CNT.value_text, CNT.note FROM person P LEFT JOIN contact CNT ON P.person = CNT.subject_fk "
					+"INNER JOIN contact_type CT ON CNT.contact_type_fk= CT.contact_type "
					+"WHERE CNT.subject_type_fk = 1 ";
			sql += "UNION ";
			sql += "SELECT E.enterprise,E.name, E.full_name,ENT_REP.first_name, ENT_REP.last_name,CT.type_name,CNT.value_text, CNT.note  "
					+"FROM enterprise E LEFT JOIN enterprise_person_relation EPR ON E.enterprise = EPR.enterprise_fk INNER JOIN person ENT_REP ON EPR.person_fk = ENT_REP.person "
					+"LEFT JOIN contact CNT ON ENT_REP.person = CNT.subject_fk INNER JOIN contact_type CT ON CNT.contact_type_fk= CT.contact_type "
					+"WHERE CNT.subject_type_fk = 1 ";
		}
		if(country != "" || county != "" || townVillage != "" || streetAddress != "" || zipcode != ""){
			if(firstName != "" || name != "" || contact != ""){
				sql += "FULL OUTER JOIN ";
			}
			String countryString = "";
			String countyString = "";
			String townVillageString = "";
			String streetAddressString = "";
			String zipcodeString ="";
			if(country != ""){
				countryString = "AND UPPER(A.country) LIKE UPPER('%"+country+"%') ";
			}
			if(county != ""){
				countyString = "AND UPPER(A.county) LIKE UPPER('%"+county+"%') ";
			}
			if(townVillage != ""){
				townVillageString = "AND UPPER(A.town_village) LIKE UPPER('%"+townVillage+"%') ";
			}
			if(streetAddress != ""){
				streetAddressString = "AND UPPER(A.street_address) UPPER('%"+streetAddress+"%') ";
			}
			if(zipcode != ""){
				zipcodeString = "AND UPPER(A.zipcode) LIKE UPPER('%"+zipcode+"%') ";
			}
			//Isiku aadresside p�ring + ettev�tte aadresside p�ring
			sql += "(SELECT P.person AS subject_id, 1 AS subject_type, P.first_name, P.last_name,AT.type_name,A.country, A.county, A.town_village, A.street_address, A.zipcode FROM person P "
					+"LEFT JOIN address A ON P.person = A.subject_fk INNER JOIN address_type AT ON A.address_type_fk = AT.address_type "
					+"WHERE A.subject_type_fk = 1 "+countryString+countyString+townVillageString+streetAddressString+zipcodeString;
			sql += "GROUP BY AT.type_name, P.person, A.country, A.county, A.town_village, A.street_address, A.zipcode UNION ";
			sql += "SELECT E.enterprise AS subject_id, 2 AS subject_type, E.name, E.full_name,AT.type_name,A.country, A.county, A.town_village, A.street_address, A.zipcode FROM enterprise E "
					+"LEFT JOIN address A ON E.enterprise = A.subject_fk INNER JOIN address_type AT ON A.address_type_fk = AT.address_type "
					+"WHERE A.subject_type_fk = 2 "+countryString+countyString+townVillageString+streetAddressString+zipcodeString;
			sql += "GROUP BY AT.type_name, E.enterprise, A.country, A.county, A.town_village, A.street_address, A.zipcode) ";
		}
		
		sql += ") AS foo;";
		System.out.println(sql);
		ResultSet resultSet = null;
		try{
			resultSet = db.executeQuery(sql);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return resultSet;
		
		
	}
	
	
}

